<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>颜色主题</title>
  <script src="./js/jexcel.js"></script>
  <link rel="stylesheet" href="./css/jexcel.css" type="text/css" />
  <link rel="stylesheet" href="./css/jexcel.themes.css" type="text/css" />
  <script src="./js/jsuites.js"></script>
  <link rel="stylesheet" href="./css/jsuites.css" type="text/css" />
  <!-- colorpicker -->
  <script src="./js/colorpicker/dist/xncolorpicker.min.js"></script>
  <!-- toolbar -->
  <link rel="stylesheet" type="text/css" href="./css/fonts.icon.css" />

  <!-- 打印print.js -->
  <script src="https://printjs-4de6.kxcdn.com/print.min.js"></script>

  <!-- 导入外部xlsx文件需要以下两个文件 -->
  <script src="./js/jszip.js"></script>
  <script src="./js/xlsx.js"></script>

  <!-- 请在CSS文件中添加以下变量 -->
  <link rel="stylesheet" type="text/css" href="./css/themes.css" />
  <style></style>
</head>

<body>
  <h1>颜色主题</h1>
  <div>
    <button type="button" id="btn1">Default</button>
    <button type="button" id="btn2">DarK</button>
    <a href="https://www.mdui.org/docs/material_icon" target="_blank" rel="noopener noreferrer">Icon</a>
    <a href="https://getkit.cn/resources/material-design-icons/" target="_blank" rel="noopener noreferrer">Icon2</a>
  </div>

  <!-- 单项定制主题 -->
  <div class="itemStyleBox">
    <!-- header -->
    <div class="itemBox">
      <div class="item">
        <div class="picker" id="colorpicker1"></div>
        Header color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker2"></div>
        Header color highlighted
      </div>
      <div class="item">
        <div class="picker" id="colorpicker3"></div>
        Header background color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker4"></div>
        Header background color highlighted
      </div>
    </div>
    <!-- menu -->
    <div class="itemBox">
      <div class="item">
        <div class="picker" id="colorpicker5"></div>
        Menu color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker6"></div>
        Menu color highlighted
      </div>
      <div class="item">
        <div class="picker" id="colorpicker7"></div>
        Menu background color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker8"></div>
        Menu background color highlighted
      </div>
    </div>
    <!-- Content -->
    <div class="itemBox">
      <div class="item">
        <div class="picker" id="colorpicker9"></div>
        Content color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker10"></div>
        Content color highlighted
      </div>
      <div class="item">
        <div class="picker" id="colorpicker11"></div>
        Content background color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker12"></div>
        Content background color highlighted
      </div>
    </div>
    <!-- Border -->
    <div class="itemBox">
      <div class="item">
        <div class="picker" id="colorpicker13"></div>
        Border color
      </div>
      <div class="item">
        <div class="picker" id="colorpicker14"></div>
        Border color highlighted
      </div>
      <div class="item">
        <div class="picker" id="colorpicker15"></div>
        Link color
      </div>
    </div>
  </div>

  <!-- 操作表格 -->
  <div style="margin-top: 20px;">
    <button type="button" id="btn3">导入外部CSV文件</button>
    <button type="button" id="btn4">导入外部XLSX文件</button>
    <button type="button" id="btn5">导入外部json文件</button>
    <button type="button" id="btn6">导入外部json对象</button>
    <button type="button" id="btn7">导出为CSV文件</button>
  </div>

  <div style="margin-top: 20px;">
    <button type="button" id="btn8">新建工作表</button>
  </div>

  <!-- 表格 -->
  <div id="spreadsheet" style="margin-top: 20px;"></div>
</body>

<script type="module">
  import { text } from "./js/language.js";
  import { openDownloadDialog, sheet2blob } from './js/exportExcel.js'

  let oBtn1 = document.getElementById('btn1');
  let oBtn2 = document.getElementById('btn2');
  let oBtn3 = document.getElementById('btn3');
  let oBtn4 = document.getElementById('btn4');
  let oBtn5 = document.getElementById('btn5');
  let oBtn6 = document.getElementById('btn6');
  let oBtn7 = document.getElementById('btn7');
  let oBtn8 = document.getElementById('btn8');

  var data = [
    ['小笑残虹', '男', '18', false],
    ['张三', '男', '23', true],
    ['小虹', '女', '16', true],
    ['小丽', '女', '18', false],
  ];

  // 使用选项卡工作表
  var sheets = [
    {
      sheetName: 'Sheet1',
      minDimensions: [10, 10]
    },
    {
      sheetName: 'Sheet2',
      data: data,
      rows: [
        {
          height: '50px'
        }
      ],
      columns: [
        {
          title: '姓名',
          type: 'text',
          width: '100px',
        },
        {
          title: '性别',
          type: 'dropdown',
          source: ['男', '女'],
          width: '100px',
        },
        {
          title: '年龄',
          type: 'text',
          width: '100px',
        },
        {
          title: '是否脱单',
          type: 'checkbox',
          width: '100px',
        },
      ],
      minDimensions: [20, 10],
      style: {
        A1: 'background-color: orange; color: #fff;',
      },
      fullscreen: false,
      text,
      // 自定义工具栏
      toolbar: [
        {
          type: 'i',
          tooltip: '撤销',
          content: 'undo',
          onclick: function () {
            table.undo();
          }
        },
        {
          type: 'i',
          tooltip: '重做',
          content: 'redo',
          onclick: function () {
            table.redo();
          }
        },
        {
          type: 'i',
          tooltip: '保存',
          content: 'save',
          onclick: function () {
            table.download();
          }
        },
        {
          type: 'select',
          tooltip: '字体样式',
          k: 'font-family',
          v: ['Arial', 'Verdana']
        },
        {
          type: 'select',
          tooltip: '文字大小',
          k: 'font-size',
          v: ['9px', '10px', '11px', '12px', '13px', '14px', '15px', '16px', '17px', '18px', '19px', '20px']
        },
        {
          type: 'i',
          tooltip: '左对齐',
          content: 'format_align_left',
          k: 'text-align',
          v: 'left'
        },
        {
          type: 'i',
          tooltip: '居中对齐',
          content: 'format_align_center',
          k: 'text-align',
          v: 'center'
        },
        {
          type: 'i',
          tooltip: '右对齐',
          content: 'format_align_right',
          k: 'text-align',
          v: 'right'
        },
        {
          type: 'i',
          tooltip: '加粗',
          content: 'format_bold',
          k: 'font-weight',
          v: 'bold'
        },
        {
          type: 'color',
          tooltip: '文字颜色',
          content: 'format_color_text',
          k: 'color'
        },
        {
          type: 'color',
          tooltip: '背景色',
          content: 'format_color_fill',
          k: 'background-color'
        },
        {
          type: 'i',
          content: 'print',
          tooltip: '打印',
          onclick: function (e) {
            let oTable = document.querySelector('.jexcel');
            oTable.id = 'oTable';
            printJS({
              printable: 'oTable',
              type: 'html',
              scanStyles: false,
              css: ['./css/jexcel.css', './css/jexcel.themes.css', './css/themes.css']
            });
          }
        },
      ],
      // 允许插入批注
      allowComments: true,
      // 自定义右键菜单
      contextMenu: (obj, x, y, e) => {
        var items = [];

        if (y == null) {
          // Insert a new column
          if (obj.options.allowInsertColumn == true) {
            items.push({
              title: obj.options.text.insertANewColumnBefore,
              onclick: function () {
                obj.insertColumn(1, parseInt(x), 1);
              }
            });
          }

          if (obj.options.allowInsertColumn == true) {
            items.push({
              title: obj.options.text.insertANewColumnAfter,
              onclick: function () {
                obj.insertColumn(1, parseInt(x), 0);
              }
            });
          }

          // Delete a column
          if (obj.options.allowDeleteColumn == true) {
            items.push({
              title: obj.options.text.deleteSelectedColumns,
              onclick: function () {
                obj.deleteColumn(obj.getSelectedColumns().length ? undefined : parseInt(x));
              }
            });
          }

          // Rename column
          if (obj.options.allowRenameColumn == true) {
            items.push({
              title: obj.options.text.renameThisColumn,
              onclick: function () {
                obj.setHeader(x);
              }
            });
          }

          // Sorting
          if (obj.options.columnSorting == true) {
            // Line
            items.push({ type: 'line' });

            items.push({
              title: obj.options.text.orderAscending,
              onclick: function () {
                obj.orderBy(x, 0);
              }
            });
            items.push({
              title: obj.options.text.orderDescending,
              onclick: function () {
                obj.orderBy(x, 1);
              }
            });
          }
        } else {
          // Insert new row
          if (obj.options.allowInsertRow == true) {
            items.push({
              title: obj.options.text.insertANewRowBefore,
              onclick: function () {
                obj.insertRow(1, parseInt(y), 1);
              }
            });

            items.push({
              title: obj.options.text.insertANewRowAfter,
              onclick: function () {
                obj.insertRow(1, parseInt(y));
              }
            });
          }

          if (obj.options.allowDeleteRow == true) {
            items.push({
              title: obj.options.text.deleteSelectedRows,
              onclick: function () {
                obj.deleteRow(obj.getSelectedRows().length ? undefined : parseInt(y));
              }
            });
          }

          if (x) {
            if (obj.options.allowComments == true) {
              items.push({ type: 'line' });

              var title = obj.records[y][x].getAttribute('title') || '';

              items.push({
                title: title ? obj.options.text.editComments : obj.options.text.addComments,
                onclick: function () {
                  var comment = prompt(obj.options.text.comments, title);
                  if (comment) {
                    obj.setComments([x, y], comment);
                  }
                }
              });

              if (title) {
                items.push({
                  title: obj.options.text.clearComments,
                  onclick: function () {
                    obj.setComments([x, y], '');
                  }
                });
              }
            }
          }
        }

        // Line
        items.push({ type: 'line' });

        // 合并单元格
        items.push({
          title: '合并单元格',
          onclick: function () {
            // 获取选择的单元格位置信息
            let selectedContainer = table.selectedContainer;
            // 根据单元格的 x, y 获取单元格名字
            let cellName1 = jexcel.getColumnNameFromId([selectedContainer[0], selectedContainer[1]]);
            // 计算需要合并的列数和行数
            let rowNum = selectedContainer[3] - selectedContainer[1] + 1;
            let colNum = selectedContainer[2] - selectedContainer[0] + 1;
            // 合并单元格
            table.setMerge(cellName1, colNum, rowNum);
          }
        });

        // 拆分合并的单元格
        items.push({
          title: '拆分单元格',
          onclick: function () {
            // 获取选择的单元格位置信息
            let selectedContainer = table.selectedContainer;
            // 根据单元格的 x, y 获取单元格名字
            let cellName1 = jexcel.getColumnNameFromId([selectedContainer[0], selectedContainer[1]]);
            table.removeMerge(cellName1);
          }
        });

        // 拆分所有的合并的单元格
        items.push({
          title: '拆分所有合并的单元格',
          onclick: function () {
            table.destroyMerged();
          }
        });

        // Line
        items.push({ type: 'line' });

        // Copy
        items.push({
          title: obj.options.text.copy,
          shortcut: 'Ctrl + C',
          onclick: function () {
            obj.copy(true);
          }
        });

        // Paste
        if (navigator && navigator.clipboard) {
          items.push({
            title: obj.options.text.paste,
            shortcut: 'Ctrl + V',
            onclick: function () {
              if (obj.selectedCell) {
                navigator.clipboard.readText().then(function (text) {
                  if (text) {
                    jexcel.current.paste(obj.selectedCell[0], obj.selectedCell[1], text);
                  }
                });
              }
            }
          });
        }

        // Save
        if (obj.options.allowExport) {
          items.push({
            title: obj.options.text.saveAs,
            shortcut: 'Ctrl + S',
            onclick: function () {
              obj.download();
            }
          });
        }

        // About
        if (obj.options.about) {
          items.push({
            title: obj.options.text.about,
            onclick: function () {
              alert('小笑残虹');
            }
          });
        }

        return items;
      },
      onselection: (instance, x1, y1, x2, y2, origin) => {
        // console.log(table);
        var cellName1 = jexcel.getColumnNameFromId([x1, y1]);
        var cellName2 = jexcel.getColumnNameFromId([x2, y2]);
        let row = table.getSelectedRows(true);
        let col = table.getSelectedColumns(true);
      },
      onload: function (instance) {
        console.log('onload instance', instance);
        // oTable.id = 'exceltable';
        // console.log('table', oTable);
      },
    },
  ];
  let isFullscreen = false;
  let table = jspreadsheet(document.getElementById('spreadsheet'), {
    csvFileName: '小笑残虹的电子表格',
    data: data,
    rows: [
      {
        height: '50px'
      }
    ],
    columns: [
      {
        title: '姓名',
        type: 'text',
        width: '100px',
      },
      {
        title: '性别',
        type: 'dropdown',
        source: ['男', '女'],
        width: '100px',
      },
      {
        title: '年龄',
        type: 'text',
        width: '100px',
      },
      {
        title: '是否脱单',
        type: 'checkbox',
        width: '100px',
      },
    ],
    minDimensions: [20, 10],
    style: {
      A1: 'background-color: orange; color: #fff;',
    },
    fullscreen: false,
    defaultColWidth: 100,
    defaultRowHeight: 30,
    defaultColAlign: 'left',
    meta: {
      isFullscreen: false,
    },
    text,
    // 自定义工具栏
    toolbar: [
      {
        type: 'i',
        tooltip: '撤销',
        content: 'undo',
        onclick: function () {
          table.undo();
        }
      },
      {
        type: 'i',
        tooltip: '重做',
        content: 'redo',
        onclick: function () {
          table.redo();
        }
      },
      {
        type: 'i',
        tooltip: '保存',
        content: 'save',
        onclick: function () {
          table.download();
        }
      },
      {
        type: 'select',
        tooltip: '字体样式',
        k: 'font-family',
        v: ['Arial', 'Verdana']
      },
      {
        type: 'select',
        tooltip: '文字大小',
        k: 'font-size',
        v: ['9px', '10px', '11px', '12px', '13px', '14px', '15px', '16px', '17px', '18px', '19px', '20px']
      },
      {
        type: 'i',
        tooltip: '左对齐',
        content: 'format_align_left',
        k: 'text-align',
        v: 'left'
      },
      {
        type: 'i',
        tooltip: '居中对齐',
        content: 'format_align_center',
        k: 'text-align',
        v: 'center'
      },
      {
        type: 'i',
        tooltip: '右对齐',
        content: 'format_align_right',
        k: 'text-align',
        v: 'right'
      },
      {
        type: 'i',
        tooltip: '加粗',
        content: 'format_bold',
        k: 'font-weight',
        v: 'bold'
      },
      {
        type: 'color',
        tooltip: '文字颜色',
        content: 'format_color_text',
        k: 'color'
      },
      {
        type: 'color',
        tooltip: '背景色',
        content: 'format_color_fill',
        k: 'background-color'
      },
      {
        type: 'i',
        content: 'print',
        tooltip: '打印',
        onclick: function (e) {
          let oTable = document.querySelector('.jexcel');
          oTable.id = 'oTable';
          printJS({
            printable: 'oTable',
            type: 'html',
            scanStyles: false,
            css: ['./css/jexcel.css', './css/jexcel.themes.css', './css/themes.css']
          });
        }
      },
      {
        type: 'i',
        tooltip: '导出为Excel',
        content: 'file_download',
        onclick: function () {
          let oTable = document.querySelector('.jexcel');
          var sheet = XLSX.utils.table_to_sheet(oTable);
          openDownloadDialog(sheet2blob(sheet), '导出为excel示例.xlsx');
        }
      },
      {
        type: 'i',
        tooltip: isFullscreen ? '非全屏模式' : '全屏模式',
        content: isFullscreen ? 'fullscreen_exit' : 'fullscreen',
        onclick: function () {
          let meta = table.getMeta();
          console.log(meta);
          isFullscreen = !isFullscreen;
          table.fullscreen(isFullscreen)
        }
      },
    ],
    // 允许插入批注
    allowComments: true,
    // 自定义右键菜单
    contextMenu: (obj, x, y, e) => {
      var items = [];

      if (y == null) {
        // Insert a new column
        if (obj.options.allowInsertColumn == true) {
          items.push({
            title: obj.options.text.insertANewColumnBefore,
            onclick: function () {
              obj.insertColumn(1, parseInt(x), 1);
            }
          });
        }

        if (obj.options.allowInsertColumn == true) {
          items.push({
            title: obj.options.text.insertANewColumnAfter,
            onclick: function () {
              obj.insertColumn(1, parseInt(x), 0);
            }
          });
        }

        // Delete a column
        if (obj.options.allowDeleteColumn == true) {
          items.push({
            title: obj.options.text.deleteSelectedColumns,
            onclick: function () {
              obj.deleteColumn(obj.getSelectedColumns().length ? undefined : parseInt(x));
            }
          });
        }

        // Rename column
        if (obj.options.allowRenameColumn == true) {
          items.push({
            title: obj.options.text.renameThisColumn,
            onclick: function () {
              obj.setHeader(x);
            }
          });
        }

        // Sorting
        if (obj.options.columnSorting == true) {
          // Line
          items.push({ type: 'line' });

          items.push({
            title: obj.options.text.orderAscending,
            onclick: function () {
              obj.orderBy(x, 0);
            }
          });
          items.push({
            title: obj.options.text.orderDescending,
            onclick: function () {
              obj.orderBy(x, 1);
            }
          });
        }
      } else {
        // Insert new row
        if (obj.options.allowInsertRow == true) {
          items.push({
            title: obj.options.text.insertANewRowBefore,
            onclick: function () {
              obj.insertRow(1, parseInt(y), 1);
            }
          });

          items.push({
            title: obj.options.text.insertANewRowAfter,
            onclick: function () {
              obj.insertRow(1, parseInt(y));
            }
          });
        }

        if (obj.options.allowDeleteRow == true) {
          items.push({
            title: obj.options.text.deleteSelectedRows,
            onclick: function () {
              obj.deleteRow(obj.getSelectedRows().length ? undefined : parseInt(y));
            }
          });
        }

        if (x) {
          if (obj.options.allowComments == true) {
            items.push({ type: 'line' });

            var title = obj.records[y][x].getAttribute('title') || '';

            items.push({
              title: title ? obj.options.text.editComments : obj.options.text.addComments,
              onclick: function () {
                var comment = prompt(obj.options.text.comments, title);
                if (comment) {
                  obj.setComments([x, y], comment);
                }
              }
            });

            if (title) {
              items.push({
                title: obj.options.text.clearComments,
                onclick: function () {
                  obj.setComments([x, y], '');
                }
              });
            }
          }
        }
      }

      // Line
      items.push({ type: 'line' });

      // 合并单元格
      items.push({
        title: '合并单元格',
        onclick: function () {
          // 获取选择的单元格位置信息
          let selectedContainer = table.selectedContainer;
          // 根据单元格的 x, y 获取单元格名字
          let cellName1 = jexcel.getColumnNameFromId([selectedContainer[0], selectedContainer[1]]);
          let cellName2 = jexcel.getColumnNameFromId([selectedContainer[2], selectedContainer[3]]);
          console.log(cellName1, cellName2);
          // 计算需要合并的列数和行数
          let rowNum = selectedContainer[3] - selectedContainer[1] + 1;
          let colNum = selectedContainer[2] - selectedContainer[0] + 1;
          console.log(table.options.mergeCells);
          if (table.options.mergeCells[cellName1]) { // 若该单元格已合并
            // 先拆分单元格
            table.removeMerge(cellName1);
            table.setMerge(cellName1, colNum, rowNum);
          } else {
            // 合并单元格
            table.setMerge(cellName1, colNum, rowNum);
          }
        }
      });

      // 拆分合并的单元格
      items.push({
        title: '拆分单元格',
        onclick: function () {
          // 获取选择的单元格位置信息
          let selectedContainer = table.selectedContainer;
          // 根据单元格的 x, y 获取单元格名字
          let cellName1 = jexcel.getColumnNameFromId([selectedContainer[0], selectedContainer[1]]);
          table.removeMerge(cellName1);
        }
      });

      // 拆分所有的合并的单元格
      items.push({
        title: '拆分所有合并的单元格',
        onclick: function () {
          table.destroyMerged();
        }
      });

      // Line
      items.push({ type: 'line' });

      // Copy
      items.push({
        title: obj.options.text.copy,
        shortcut: 'Ctrl + C',
        onclick: function () {
          obj.copy(true);
        }
      });

      // Paste
      if (navigator && navigator.clipboard) {
        items.push({
          title: obj.options.text.paste,
          shortcut: 'Ctrl + V',
          onclick: function () {
            if (obj.selectedCell) {
              navigator.clipboard.readText().then(function (text) {
                if (text) {
                  jexcel.current.paste(obj.selectedCell[0], obj.selectedCell[1], text);
                }
              });
            }
          }
        });
      }

      // Save
      if (obj.options.allowExport) {
        items.push({
          title: obj.options.text.saveAs,
          shortcut: 'Ctrl + S',
          onclick: function () {
            obj.download();
          }
        });
      }

      // Save as excel
      if (obj.options.allowExport) {
        items.push({
          title: '保存为Excel',
          onclick: function () {
            let oTable = document.querySelector('.jexcel');
            var sheet = XLSX.utils.table_to_sheet(oTable);
            openDownloadDialog(sheet2blob(sheet), `${obj.options.csvFileName}.xlsx`);
          }
        });
      }

      // About
      if (obj.options.about) {
        items.push({
          title: obj.options.text.about,
          onclick: function () {
            alert('小笑残虹');
          }
        });
      }

      return items;
    },
    onselection: (instance, x1, y1, x2, y2, origin) => {
      var cellName1 = jexcel.getColumnNameFromId([x1, y1]);
      var cellName2 = jexcel.getColumnNameFromId([x2, y2]);
      let row = table.getSelectedRows(true);
      let col = table.getSelectedColumns(true);
    },
    onload: function (instance) {
      console.log('onload instance', instance);
      // oTable.id = 'exceltable';
      // console.log('table', oTable);
    },
  });

  // 使用选项卡工作表
  // let table = jspreadsheet.tabs(document.getElementById('spreadsheet'), sheets);

  // Default
  oBtn1.onclick = function () {
    setStyle('--jexcel_header_color', '#000');
    setStyle('--jexcel_header_color_highlighted', '#000');
    setStyle('--jexcel_header_background', '#f3f3f3');
    setStyle('--jexcel_header_background_highlighted', '#dcdcdc');

    setStyle('--jexcel_content_color', '#000');
    setStyle('--jexcel_content_color_highlighted', '#000');
    setStyle('--jexcel_content_background', '#fff');
    setStyle('--jexcel_content_background_highlighted', 'rgba(0, 0, 0, 0.05)');

    setStyle('--jexcel_menu_background', '#fff');
    setStyle('--jexcel_menu_background_highlighted', '#ebebeb');
    setStyle('--jexcel_menu_color', '#555');
    setStyle('--jexcel_menu_color_highlighted', '#555');
    setStyle('--jexcel_menu_box_shadow', '2px 2px 2px 0px rgba(143, 144, 145, 1)');

    setStyle('--jexcel_border_color', '#ccc');
    setStyle('--jexcel_border_color_highlighted', '#000');
    setStyle('--active_color', '#007aff');

    headerColor.setColor(document.body.style.getPropertyValue('--jexcel_header_color').trim())
  }

  // Dark
  oBtn2.onclick = function () {
    setStyle('--jexcel_header_color', '#888');
    setStyle('--jexcel_header_color_highlighted', '#444');
    setStyle('--jexcel_header_background', '#313131');
    setStyle('--jexcel_header_background_highlighted', '#777');

    setStyle('--jexcel_content_color', '#777');
    setStyle('--jexcel_content_color_highlighted', '#333');
    setStyle('--jexcel_content_background', '#3e3e3e');
    setStyle('--jexcel_content_background_highlighted', '#333');

    setStyle('--jexcel_menu_background', '#7e7e7e');
    setStyle('--jexcel_menu_background_highlighted', '#ebebeb');
    setStyle('--jexcel_menu_color', '#ddd');
    setStyle('--jexcel_menu_color_highlighted', '#222');
    setStyle('--jexcel_menu_box_shadow', 'unset');

    setStyle('--jexcel_border_color', '#5f5f5f');
    setStyle('--jexcel_border_color_highlighted', '#999');
    setStyle('--active_color', '#eee');

    headerColor.setColor(document.body.style.getPropertyValue('--jexcel_header_color').trim())
  }

  // 导入外部CSV文件
  oBtn3.onclick = function () {
    document.getElementById('spreadsheet').innerHTML = '';
    let table = jspreadsheet(document.getElementById('spreadsheet'), {
      csv: './static/1.csv',
      csvHeaders: true,
      columns: [
        { type: 'text', width: 100 },
        { type: 'text', width: 100 },
        { type: 'text', width: 100 },
      ]
    })
  }

  // 导入外部XLSX文件
  oBtn4.onclick = function () {
    document.getElementById('spreadsheet').innerHTML = '';
    jexcel.fromSpreadsheet('./static/1.xlsx', function (result) {
      if (!result.length) {
        console.error('jspreadsheet: Something went wrong.');
      } else {
        if (result.length == 1) {
          jspreadsheet(document.getElementById('spreadsheet'), { ...result[0], text });
        } else {
          jexcel.createTabs(document.getElementById('spreadsheet'), result);
        }
      }
    });
  }

  // 导入外部json文件
  oBtn5.onclick = function () {
    document.getElementById('spreadsheet').innerHTML = '';
    let table = jspreadsheet(document.getElementById('spreadsheet'), {
      url: './static/1.json',
      text,
      columns: [
        { type: 'text', width: 300 },
        { type: 'text', width: 100 },
      ]
    });
  }

  // 导入外部json对象
  oBtn6.onclick = function () {
    document.getElementById('spreadsheet').innerHTML = '';
    let table = jspreadsheet(document.getElementById('spreadsheet'), {
      data: [
        {
          name: 'Paulo',
          id: '3',
          age: '40',
          gender: 'Male'
        },
        {
          name: 'Cosme Sergio',
          id: '4',
          age: '48',
          gender: 'Male'
        },
        {
          name: 'Jorgina Santos',
          id: '5',
          age: '32',
          gender: 'Female'
        },
      ],
      text,
      columns: [
        {
          type: 'text',
          width: '40',
          name: 'id',
          title: 'Id',
        },
        {
          type: 'text',
          width: '200',
          name: 'name',
          title: 'Name',
        },
        {
          type: 'text',
          width: '100',
          name: 'age',
          title: 'Age',
        },
        {
          type: 'hidden',
          name: 'gender'
        },
      ],
    });
  }

  // 导出为CSV文件
  oBtn7.onclick = function () {
    table.download();
  }

  // 新建工作表
  oBtn8.onclick = function () {
    var sheets = [];

    sheets.push({
      sheetName: prompt('创建一个新的工作表', '工作表 ' + document.getElementById('spreadsheet').jexcel.length),
      minDimensions: [10, 10]
    });

    jspreadsheet.tabs(document.getElementById('spreadsheet'), sheets);
  }

  let headerColor = new XNColorPicker({
    color: getStyleByKey('--jexcel_header_color'),
    selector: "#colorpicker1",
    showprecolor: true,//显示预制颜色
    prevcolors: null,//预制颜色，不设置则默认
    showhistorycolor: true,//显示历史
    historycolornum: 16,//历史条数
    format: 'rgba',//rgba hex hsla,初始颜色类型
    showPalette: true,//显示色盘
    show: false, //初始化显示
    lang: 'cn',// cn 、en
    colorTypeOption: 'single,linear-gradient,radial-gradient',
    canMove: false,//选择器位置是否可以拖拽
    alwaysShow: false,
    autoConfirm: true,
    onError: function (e) { },
    onCancel: function (color) {
      console.log("cancel", color)
    },
    onChange: function (color) {
      console.log("change", color)
    },
    onConfirm: function (val) {
      console.log("confirm", val)
      setStyle('--jexcel_header_color', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_header_color_highlighted'),
    selector: "#colorpicker2",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_header_color_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_header_background'),
    selector: "#colorpicker3",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_header_background', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_header_background_highlighted'),
    selector: "#colorpicker4",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_header_background_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_menu_color'),
    selector: "#colorpicker5",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_menu_color', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_menu_color_highlighted'),
    selector: "#colorpicker6",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_menu_color_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_menu_background'),
    selector: "#colorpicker7",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_menu_background', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_menu_background_highlighted'),
    selector: "#colorpicker8",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_menu_background_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_content_color'),
    selector: "#colorpicker9",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_content_color', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_content_color_highlighted'),
    selector: "#colorpicker10",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_content_color_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_content_background'),
    selector: "#colorpicker11",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_content_background', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_content_background_highlighted'),
    selector: "#colorpicker12",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_content_background_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_border_color'),
    selector: "#colorpicker13",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_border_color', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--jexcel_border_color_highlighted'),
    selector: "#colorpicker14",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--jexcel_border_color_highlighted', val.color.rgba)
    }
  })
  new XNColorPicker({
    color: getStyleByKey('--active_color'),
    selector: "#colorpicker15",
    onError: function (e) { },
    onCancel: function (color) { },
    onChange: function (color) { },
    onConfirm: function (val) {
      setStyle('--active_color', val.color.rgba)
    }
  })

  // 设置样式
  function setStyle(key, val) {
    document.body.style.setProperty(key, val);
  }

  // 获取变量样式
  function getStyleByKey(key) {
    // let style = document.body.style.getPropertyValue(key).trim();
    let root = getComputedStyle(document.documentElement);
    var style = root.getPropertyValue(key).trim();
    // console.log(style);
    return style
  }

  // 删除变量样式
  function delStyleByKey(key) {
    document.body.style.removeProperty(key);
  }
</script>

</html>